package com.klnst.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.klnst.task.XmlReader;
import com.klnst.util.Tools;

public class MysqlDbReader {

	private String url = "";// 数据库连接字符串
	private String username = "";// 数据库用户名
	private String password = "";// 数据库密码
	
	public MysqlDbReader(String filePath){
		init(filePath);
	}
	public void init(String filePath){
		Map<String, String> map = XmlReader.readDbInfo(filePath);
		url = map.get("connectionURL");
		username = map.get("userId");
		password = map.get("password");
	}
	/**
	 * <br>
	 * 方法说明：获得数据连接 <br>
	 * 输入参数： <br>
	 * 返回类型：Connection 连接对象
	 */
//	public Connection conn() {
//		try {
//			// 第一步：加载JDBC驱动
//			Class.forName("com.mysql.jdbc.Driver");
//			// 第二步：创建数据库连接
//			Connection con = DriverManager.getConnection(url, username,password);
//			return con;
//		} catch (ClassNotFoundException cnf) {
//			System.out.println("driver not find:" + cnf);
//			return null;
//		} catch (SQLException sqle) {
//			System.out.println("can't connection db:" + sqle);
//			return null;
//		} catch (Exception e) {
//			System.out.println("Failed to load JDBC/ODBC driver.");
//			return null;
//		}
//	}
	
	public Connection conn() {
		try {
			// 第一步：加载JDBC驱动
			Class.forName("com.pivotal.jdbc.GreenplumDriver");
			// 第二步：创建数据库连接
			Connection con = DriverManager.getConnection(url, username,password);
			return con;
		} catch (ClassNotFoundException cnf) {
			System.out.println("driver not find:" + cnf);
			return null;
		} catch (SQLException sqle) {
			System.out.println("can't connection db:" + sqle);
			return null;
		} catch (Exception e) {
			System.out.println("Failed to load JDBC/ODBC driver.");
			return null;
		}
	}
	
	/**
	 * <br>
	 * 方法说明：执行查询SQL语句 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String sql 要执行的SQL语句 <br>
	 * 返回类型：void
	 * @throws SQLException 
	 */
	public ResultSet query(Connection con, String sql) throws SQLException {
		if (con == null) {
			throw new SQLException("数据库连接异常，请检查数据库状态或连接参数");
		}
		if (sql == null)
			throw new SQLException("check your parameter: 'sql'! don't input null!");
		// 第三步：获取Staetment对象
		try{
			Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			// 第四步：执行数据库操作（查询操作）
			ResultSet rs = stmt.executeQuery(sql);
			// 第五步：处理结果集
			ResultSetMetaData rmeta = rs.getMetaData();
			// 获得数据字段个数
			int numColumns = rmeta.getColumnCount();
			return rs;
		}catch(SQLException e){
			throw new SQLException("执行"+sql+"时发生异常！");
		}catch(Exception e){
			throw new SQLException("执行"+sql+"时发生异常！！！");
		}
	}
	
	/**
	 * <br>
	 * 方法说明：执行插入、更新、删除等没有返回结果集的SQL语句 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String sql 要执行的SQL语句 <br>
	 * 返回类型：void
	 */
	public void execute(Connection con, String sql) {
		try {
			if (con == null)
				return;
			// 第三步：获取Statement对象
			Statement stmt = con.createStatement();
			// 第四步：执行数据库操作（更新操作）
			stmt.executeUpdate(sql);
			System.out.println("update executed successly");
		} catch (Exception e) {
			System.out.println("execute error: sql = " + sql);
			System.out.println(e);
		}// end try catch
	}// end execute

	public boolean testConnection() throws SQLException {
		int rowcount=0;
		ResultSet rs = query(conn(), "select 1 from dual");
		if(rs==null){
			return false;
		}else{
			rs.last() ; 
			rowcount = rs.getRow();
		}
		if(rowcount<1) 
			return false;
		else
			return true;
	}
	
	public List<String> getTables() throws SQLException{
		List<String> list = new ArrayList<String>();
		int rowcount=0;
		ResultSet rs = query(conn(), "SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME DESC ");
		while(rs.next()){
			list.add(rs.getString(1));
		}
		return list;
	}
	/**
	 * 
	 * @Title: getTableColumn 
	 * @Description: 根据表名取表中所有字段属性
	 * @param tableName
	 * @return
	 * @throws SQLException
	 * @return List<TableColumn>    返回类型 
	 * @throws
	 */
	public List<TableColumn> getTableColumn(String tableName) throws SQLException{
		List<TableColumn> list = new ArrayList<TableColumn>();
		ResultSet rs = query(conn(), "select column_name,column_comment,data_type,character_maximum_length,character_octet_length,column_type from information_schema.`COLUMNS` where TABLE_SCHEMA='zhzs_yzym' and table_name= '"+tableName+"'");
		while(rs.next()){
			TableColumn c = new TableColumn();
			c.setFieldName(rs.getString(1));
			c.setCluComment(rs.getString(2));
			c.setDataType(rs.getString(3));
			c.setCharacterMaximumLength(rs.getString(4));
			c.setCharacterOctetLength(rs.getString(5));
			c.setCluType(rs.getString(6));
			c.setJavaField(Tools.transDbField2JavaField(rs.getString(1)));
			c.setJavaType(dbTypeTrans2JavaType(rs.getString(3)));
			c.setJdbcType(dbTypeTrans2jdbcType(rs.getString(3)));
			list.add(c);
		}
		return list;
	}
    public List<TableProperty> getTableField(String tName)  throws SQLException{
		List<TableProperty> list = new ArrayList<TableProperty>();
		int rowcount=0;
		ResultSet rs = query(conn(), "SELECT COL.COLUMN_NAME,COL.DATA_TYPE,COL.DATA_LENGTH,COL.NULLABLE,COL.DATA_PRECISION,COL.DATA_SCALE,(SELECT A.CONSTRAINT_TYPE FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.TABLE_NAME = COL.TABLE_NAME AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.CONSTRAINT_TYPE = 'P' AND B.COLUMN_NAME=COL.COLUMN_NAME) AS PK,(SELECT COMMENTS FROM  USER_COL_COMMENTS COMM WHERE COMM.TABLE_NAME=COL.TABLE_NAME AND COMM.COLUMN_NAME=COL.COLUMN_NAME) AS COMMENTS FROM COLS COL WHERE COL.TABLE_NAME = '" + tName +"'");
		while(rs.next()){
			TableProperty table=new TableProperty();
			table.setName(tName);
			table.setFieldName(rs.getString(1));
			table.setFieldType(rs.getString(2));
			if(String.valueOf(rs.getString(2)).toUpperCase().indexOf("NUMBER")>-1){
				table.setFieldLength(StringUtils.isBlank(rs.getString(5))?"38":rs.getString(5));
				table.setPrecision(StringUtils.isBlank(rs.getString(5))?"38":rs.getString(5));
				table.setScale(StringUtils.isBlank(rs.getString(6))?"0":rs.getString(6));
			}else{
				table.setFieldLength(String.valueOf(rs.getInt(3)));
			}
			table.setNullable(rs.getString(4));
			table.setJavaField(Tools.transDbField2JavaField(rs.getString(1)));
			table.setJavaType(dbTypeTrans2JavaType(rs.getString(2),rs.getString(5),rs.getString(6)));
			table.setPk(rs.getString(7));
			table.setComments(rs.getString(8));
			list.add(table);
		}
		return list;
	}
	
	public String dbTypeTrans2JavaType(String dbType,String precision,String scale){
		String javaType;
		if(dbType.toUpperCase().indexOf("CHAR")>-1){
			javaType="String";
		}else if(dbType.toUpperCase().indexOf("DECIMAL")>-1){
			javaType="double";
		}else if(dbType.toUpperCase().indexOf("INT")>-1){
			javaType="int";
		}else if(dbType.toUpperCase().indexOf("DATE")>-1){
			javaType="Date";
		}else{
			javaType="String";
		}
		return javaType;
	}
	public String dbTypeTrans2JavaType(String dbType){
		String javaType;
		if(dbType.toUpperCase().indexOf("CHAR")>-1){
			javaType="String";
		}else if(dbType.toUpperCase().indexOf("DECIMAL")>-1){
			javaType="double";
		}else if(dbType.toUpperCase().indexOf("INT")>-1){
			javaType="int";
		}else if(dbType.toUpperCase().indexOf("DATE")>-1){
			javaType="Date";
		}else{
			javaType="String";
		}
		return javaType;
	}
	public String dbTypeTrans2jdbcType(String dbType){
		String javaType;
		if(dbType.toUpperCase().indexOf("CHAR")>-1){
			javaType="VARCHAR";
		}else if(dbType.toUpperCase().indexOf("DECIMAL")>-1){
			javaType="INTEGER";
		}else if(dbType.toUpperCase().indexOf("INT")>-1){
			javaType="INTEGER";
		}else if(dbType.toUpperCase().indexOf("DATE")>-1){
			javaType="DATE";
		}else{
			javaType="VARCHAR";
		}
		return javaType;
	}

}
